<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="support.*, java.util.*,  java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
	<link rel="stylesheet" type="text/css" href="../resource/styleCSS.css" /> 	
	<link rel="stylesheet" type="text/css" href="../resource/columnCSS.css" /> 
	<title>Graduate School Application - Specialization Analytics</title>
</head>
<body>
	<div class="container">
	<div class="center_div">
		<%		
			try {
				//Registering Postgresql JDBC driver
				Class.forName("org.postgresql.Driver");
			} catch (ClassNotFoundException e) {
				System.err.println("ClassNotFoundException: " + e);
			} 
			try {	
				//Open a connection to the database
				Connection conn = DriverManager.getConnection(
						"jdbc:postgresql://localhost:5432/CSE135_SP11?"
						+ "user=postgres&password=postgres");
		
	
				Statement stmt = conn.createStatement();

				support s = new support();
			 	String path = config.getServletContext().getRealPath("specializations.txt");
				Vector specs = s.getCountries(path);
				
				
				// Wrong query to execute -- need to execute query that is right for each 
				// possibility
				ResultSet rs;
	
				int numOfApplicants = 0;

				String url = request.getRequestURI();
				int len = url.length();
				url = url.substring(0, len - 30);
				url = url.concat("applicationsJSP.jsp?spec=");
		%>
		
		<div class="colmask threecol">
		<div class="colmid">
		<div class="colleft">
			<div class="bodycol">
				<p><b>Select Applicants to view from a Specialization:</b></p>
			</div>
			<div class="sidecol">
				<p><a href="../invalidate.jsp">Logout</a></p>
			</div>
		</div>
		</div>
		</div>
		<div style="clear:both;"></div>
		
		<ul>
			<%
				for(int i = 0; i < specs.size(); i++)
				{
					rs = stmt.executeQuery("SELECT DISTINCT first_name FROM Students s, Specializations sp " +
								"WHERE s.specialization=sp.id AND sp.specialization=\'"
								+ specs.get(i) + "\'");
					while(rs.next())
						numOfApplicants++;
							
					out.println("<li>" + specs.get(i) + " <a href=\"" 
						+ url + specs.get(i) + "\">" + 
						numOfApplicants + "</a></li>");
					numOfApplicants = 0;
				}
			%>
		</ul>
		<% 
				stmt.close();
				conn.close();
			
				} catch (SQLException e) {
					System.err.println("SQLException: " + e);
				}
		%>
	</div>
	</div>
</body>
</html>